﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccessLayer
{
    public static class CompanyDB
    {
        public static List<Company> ShowAll()
        {
            List<Company> myCompanies = new List<Company>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * From Company";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Company company = new Company();
                    company.Id = (int)reader["Id"];
                    company.Name = reader["Name"].ToString();
                    company.Street = reader["Street"].ToString();
                    company.Zipcode = (int)reader["Zipcode"];
                    company.City = reader["City"].ToString();
                    company.Country = reader["Country"].ToString();
                    company.Email = reader["Email"].ToString();
                    company.Phone = reader["Phone"].ToString();
                    company.Employees = reader["Employees"].ToString();
                    myCompanies.Add(company);
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return myCompanies;
        }

        public static int DeleteCompany(Company c)
        {

            int aantal;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "DELETE FROM Company WHERE Id = @Company_Id;";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query               
                command.Parameters.AddWithValue("@Company_Id", c.Id);

                //query uitvoeren
                aantal = command.ExecuteNonQuery();

            }
            catch (NullReferenceException ex)
            {
                return aantal = 0;
            }

            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static int InsertCompany(Company c)
        {

            int aantal;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "Insert into Company (Name, Street, Zipcode, City, Country, Email, Phone, Employees) VALUES (@Name, @Street, @Zipcode, @City, @Country, @Email, @Phone, @Employees)";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query
                command.Parameters.AddWithValue("@Name", c.Name);
                command.Parameters.AddWithValue("@Street", c.Street);
                command.Parameters.AddWithValue("@Zipcode", c.Zipcode);
                command.Parameters.AddWithValue("@City", c.City);
                command.Parameters.AddWithValue("@Country", c.Country);
                command.Parameters.AddWithValue("@Email", c.Email);
                command.Parameters.AddWithValue("@Phone", c.Phone);
                command.Parameters.AddWithValue("@Employees", c.Employees);

                //query uitvoeren
                aantal = command.ExecuteNonQuery();

            }

            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static int UpdateCompany(Company c)
        {

            int aantal;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "Update Company Set Name=@Name, Street=@Street, Zipcode=@Zipcode, City=@City, Country=@Country, Email=@Email, Phone=@Phone, Employees=@Employees WHERE Id=@Id";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query
                command.Parameters.AddWithValue("@Id", c.Id);
                command.Parameters.AddWithValue("@Name", c.Name);
                command.Parameters.AddWithValue("@Street", c.Street);
                command.Parameters.AddWithValue("@Zipcode", c.Zipcode);
                command.Parameters.AddWithValue("@City", c.City);
                command.Parameters.AddWithValue("@Country", c.Country);
                command.Parameters.AddWithValue("@Email", c.Email);
                command.Parameters.AddWithValue("@Phone", c.Phone);
                command.Parameters.AddWithValue("@Employees", c.Employees);

                //query uitvoeren
                aantal = command.ExecuteNonQuery();

            }

            catch (InvalidOperationException exception)
            {
                throw;
            }

            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static int GetCompanyIdByName(string CompanyName)
        {
            int CompanyId = 0;
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT Id From Company WHERE Name = @Company_Name;";            
            cmd.Parameters.AddWithValue("@Company_Name", CompanyName);
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    CompanyId = (int)reader["Id"];
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return CompanyId;
        }
    }
}
